package com.gitee.ice1938.adcode.fun;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import javax.annotation.Resource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Order;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;

/**
 * 
 */
@Repository
public class DictAdministrativeAreaRepository {
	private static final Logger logger = LoggerFactory.getLogger(DictAdministrativeAreaRepository.class);
	@Resource
	private JdbcTemplate jdbcTemplate;

	public List<DictAdCodeModel> findAll() {
		String sql = "Select * FROM sys_dict_adcode sda ";
		List<DictAdCodeModel> content = jdbcTemplate.query(sql.toString(),
				BeanPropertyRowMapper.newInstance(DictAdCodeModel.class));
		return content;
	}

	public Page<DictAdCodeModel> queryPage(Map<String, Object> params, Pageable pagebale, Order order) {
		String keyword = (String) params.get("q_search_key");
		String year = (String) params.get("q_year");
		StringBuffer sql = new StringBuffer("Select * FROM sys_dict_adcode sda  where 1=1");

		List<Object> param = new ArrayList<Object>();
		if (keyword != null && !keyword.isEmpty()) {
			sql.append(" and (sda.ad_code like ? ");
			param.add(keyword + "%");
			sql.append(" or sda.ad_name like ? )");
			param.add("%" + keyword + "%");
		}
		if (!ObjectUtils.isEmpty(year)) {
			sql.append(" and sda.year_time <= ? ");
//			Calendar rightNow = Calendar.getInstance();
//			rightNow.set(Calendar.YEAR, Integer.valueOf(year));
//			rightNow.set(Calendar.MONTH, 11);
//			rightNow.set(Calendar.DAY_OF_YEAR,31);
//			param.add(rightNow.getTime());
			param.add(Integer.valueOf(year));
		}

		String sqlCount = builderCountSql(sql.toString());

		if (order != null) {
			sql.append(" ORDER BY ");
			sql.append(underscoreName(order.getProperty()));
			sql.append(" ");
			sql.append(order.getDirection());
		}
		if (pagebale != null) {
			sql.append(" LIMIT ");
			sql.append(pagebale.getPageNumber() * pagebale.getPageSize());
			sql.append(", ");
			sql.append(pagebale.getPageSize());
		}

		List<DictAdCodeModel> content = jdbcTemplate.query(sql.toString(),
				BeanPropertyRowMapper.newInstance(DictAdCodeModel.class), param.toArray());
		long total = jdbcTemplate.queryForObject(sqlCount.toString(), Long.class, param.toArray());
		Page<DictAdCodeModel> retData = new PageImpl<DictAdCodeModel>(content, pagebale, total);
		return retData;
	}

//
//	public int insert(DictAdministrativeAreaModel params, String userPrincipal) {
//		String sql = "insert into sys_dict_adcode ( area_one,area_two,area_three,year,area_code,zip_code,update_user) Values (?,?,?,?,?,?,?)";
//		return jdbcTemplate.update(sql, params.getAreaOne(), params.getAreaTwo(), params.getAreaThree(),
//		        params.getYear(), params.getAreaCode(), params.getZipCode(), userPrincipal);
//	}
//
	public int updateCodeModifiedTime(int codeModifiedTime) {
		String sql = "update sys_dict_adcode set year_time = ? where revoke_time is null ";
		return jdbcTemplate.update(sql, codeModifiedTime);
	}

	@Transactional // batch 必须声明事物，否则不会达到批量的目的
	public void batchRevoke(List<DictAdCodeModel> data, String userPrincipal) {
		String sql = "update sys_dict_adcode set revoke_time = ? where ad_code = ? and use_count =?";

		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				DictAdCodeModel item = data.get(i);
				int j = 1;
				ps.setDate(j++, new java.sql.Date(item.getRevokeTime().getTime()));
				ps.setString(j++, item.getAdCode());
				ps.setInt(j++, item.getUseCount());
			}

			@Override
			public int getBatchSize() {
				return data.size();
			}
		});

	}

	@Transactional // batch 必须声明事物，否则不会达到批量的目的
	public void batchInsert(List<DictAdCodeModel> data, String userPrincipal) {
		String sql = "insert into sys_dict_adcode (ad_code,use_count,ad_name,start_time) Values (?,?,?,?)";

		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				DictAdCodeModel item = data.get(i);
				int j = 1;
				ps.setString(j++, item.getAdCode());
				ps.setInt(j++, item.getUseCount());
				ps.setString(j++, item.getAdName());
				ps.setDate(j++, new java.sql.Date(item.getStartTime().getTime()));
			}

			@Override
			public int getBatchSize() {
				return data.size();
			}
		});

	}

	/**
	 * 
	 * @param areaOne
	 * @param year
	 * @return
	 */
	public List<DictAdCodeModel> queryForCityByYear(String areaOnelike, int year) {
		String sql = "Select * FROM sys_dict_adcode where ad_code like ? and year_time <=? and revoke_time is null";
//		Calendar rightNow = Calendar.getInstance();
//		rightNow.set(Calendar.YEAR, Integer.valueOf(year));
//		rightNow.set(Calendar.MONTH, 11);
//		rightNow.set(Calendar.DAY_OF_YEAR,31);
		List<DictAdCodeModel> content = jdbcTemplate.query(sql, 
				BeanPropertyRowMapper.newInstance(DictAdCodeModel.class),new Object[] { areaOnelike, year });
		return content;

	}

	public int getLastYear() {
		try {

			Integer year = jdbcTemplate.queryForObject(
					"Select max(year_time) FROM sys_dict_adcode where year_time < ? ", Integer.class,
					"9999");
			return year;
		} catch (Exception e) {
			return -1;
		}
	}

	///////////////
	/**
	 * Convert the given name to lower case. By default, conversions will happen
	 * within the US locale.
	 * 
	 * @param name the original name
	 * @return the converted name
	 * @since 4.2
	 */
	protected String lowerCaseName(String name) {
		return name.toLowerCase(Locale.US);
	}

	/**
	 * Convert a name in camelCase to an underscored name in lower case. Any upper
	 * case letters are converted to lower case with a preceding underscore.
	 * 
	 * @param name the original name
	 * @return the converted name
	 * @since 4.2
	 * @see #lowerCaseName
	 */
	protected String underscoreName(String name) {
		if (!StringUtils.hasLength(name)) {
			return "";
		}
		StringBuilder result = new StringBuilder();
		result.append(lowerCaseName(name.substring(0, 1)));
		for (int i = 1; i < name.length(); i++) {
			String s = name.substring(i, i + 1);
			String slc = lowerCaseName(s);
			if (!s.equals(slc)) {
				result.append("_").append(slc);
			} else {
				result.append(s);
			}
		}
		return result.toString();
	}

	protected String builderCountSql(String sql) {
		return "SELECT COUNT(*) " + sql.substring(sql.toUpperCase().indexOf("FROM "));
	}

	/**
	 * 
	 * @param pageable
	 * @param sort     pageable.getSort()
	 * @param sql
	 * @return
	 */
	protected String builderPageableSql(Pageable pageable, Sort sort, StringBuilder sql) {
		String sqlCount = builderCountSql(sql.toString());
		if (sort != null && sort.isSorted()) {
			sql.append(" ORDER BY ");
			for (Iterator<Order> iterator = sort.iterator(); iterator.hasNext();) {
				Order order = iterator.next();
				sql.append(underscoreName(order.getProperty()));
				sql.append(" ");
				sql.append(order.getDirection());
				if (iterator.hasNext()) {
					sql.append(",");
				}
			}
		}
		//
		if (pageable != null && pageable.isPaged()) {
			sql.append(" LIMIT ");
			sql.append(pageable.getPageNumber() * pageable.getPageSize());
			sql.append(", ");
			sql.append(pageable.getPageSize());
		}

		return sqlCount;
	}

	public static String toCamelCaseString(String inputString, boolean firstCharacterUppercase) {
		StringBuilder sb = new StringBuilder();

		boolean nextUpperCase = false;
		for (int i = 0; i < inputString.length(); i++) {
			char c = inputString.charAt(i);

			switch (c) {
			case '_':
			case '-':
			case '@':
			case '$':
			case '#':
			case ' ':
			case '/':
			case '&':
				if (sb.length() > 0) {
					nextUpperCase = true;
				}
				break;

			default:
				if (nextUpperCase) {
					sb.append(Character.toUpperCase(c));
					nextUpperCase = false;
				} else {
					sb.append(Character.toLowerCase(c));
				}
				break;
			}
		}

		if (firstCharacterUppercase) {
			sb.setCharAt(0, Character.toUpperCase(sb.charAt(0)));
		}

		return sb.toString();
	}

}
